﻿using System;
using System.Data;
using System.Data.SQLite;

namespace YanZhiwei.DotNet.SQLite.Utilities
{
    /// <summary>
    /// SQLite帮助类
    /// </summary>
    public class SQLiteHelper
    {
        #region 构造函数以及变量
        string connectionString = string.Empty;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbpath">db路径</param>
        public SQLiteHelper(string dbpath)
        {
            connectionString = string.Format(@"Data Source={0};Pooling=true;FailIfMissing=false", dbpath);
        }
        #endregion
        #region 执行sql语句，返回影响行数
        /// <summary>
        /// 执行sql语句，返回影响行数
        ///<para>eg: string sql = "INSERT INTO Test(Name,TypeName)values(@Name,@TypeName)";   </para> 
        ///<para>SQLiteParameter[] parameters = new SQLiteParameter[]{   new SQLiteParameter("@Name",c+i.ToString()),  new SQLiteParameter("@TypeName",c.ToString())} </para>
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
        {
            int _affectedRows = -1;
            using (SQLiteConnection sqlcon = new SQLiteConnection(connectionString))
            {
                sqlcon.Open();
                using (SQLiteCommand sqlcmd = new SQLiteCommand(sql, sqlcon))
                {
                    if (parameters != null)
                        sqlcmd.Parameters.AddRange(parameters);
                    _affectedRows = sqlcmd.ExecuteNonQuery();
                }
            }
            return _affectedRows;
        }
        #endregion 
        #region ExecuteReader
        /// <summary>
        /// ExecuteReader
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>IDataReader</returns>
        public IDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
        {
            SQLiteConnection _sqlcon = new SQLiteConnection(connectionString);
            using (SQLiteCommand _sqlcmd = new SQLiteCommand(sql, _sqlcon))
            {
                if (parameters != null)
                    _sqlcmd.Parameters.AddRange(parameters);
                _sqlcon.Open();
                return _sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        #endregion 
        #region ExecuteDataTable
        /// <summary>
        /// ExecuteDataTable
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>DataTable</returns>
        public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection _sqlcon = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand _sqlcmd = new SQLiteCommand(sql, _sqlcon))
                {
                    if (parameters != null)
                        _sqlcmd.Parameters.AddRange(parameters);
                    using (SQLiteDataAdapter _sqldap = new SQLiteDataAdapter(_sqlcmd))
                    {
                        DataTable _dt = new DataTable();
                        _sqldap.Fill(_dt);
                        return _dt;
                    }
                }
            }

        }
        #endregion 
        #region ExecuteScalar
        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>Object</returns>
        public Object ExecuteScalar(string sql, SQLiteParameter[] parameters)
        {
            using (SQLiteConnection _sqlcon = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand _sqlcmd = new SQLiteCommand(sql, _sqlcon))
                {
                    if (parameters != null)
                        _sqlcmd.Parameters.AddRange(parameters);
                    _sqlcon.Open();
                    return _sqlcmd.ExecuteScalar(CommandBehavior.CloseConnection);
                }
            }
        }
        #endregion 
    }
}
